import datetime
import time
from datetime import timedelta
from datetime import datetime as dt

from dateutil.relativedelta import relativedelta


from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys

from Case.python_ui.Common.basepage import Basepage
from Case.python_ui.Common.commontool import CommonTool
from Case.python_ui.PageLocate.admin_dashboards_locator import AdminDashboardsLocator
from Case.python_ui.Test_data.common_data import Commondata


class AdminDashboardsPages(Basepage):
    query_param = {
        "Customer Resource": {
            "start_time": "01-02-2024",
            "end_time": "29-02-2024",
            "customer_name": "ye yu",
            "user_account": "1113021478@qq.com",
            "product": "Resource Orchestration Service",
        },
        "Users": {
            "start_time": "01-02-2024",
            "end_time": "29-02-2024",
        }
    }

    assert_data = {
        "timezone": "(UTC+08:00)"
    }

    query_sql = {
        "Users": {
            "total user": {
                "main": """select count(*) from user_profile up inner join user_user_customer uuc on up.user_code = uuc.user_code 
                where user_type != '04' and user_type != '02' and up.status != '99' and user_domain = 'C' and up.create_time ${time_condition}""",
                "son": """select count(cp.customer_code) from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id
                where cp.approval_state = 'APPROVAL' and cp.status = 'A' and ca.approval_date ${time_condition} """
            },
            "enterprise user": {
                "main": """select count(*) from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id
                where ca.approval_state = 'APPROVAL' and cp.status = 'A' and cp.cust_type = 'C' and ca.approval_date ${time_condition}""",
                "son": """select count(*) from user_profile up inner join user_user_customer uuc on up.user_code = uuc.user_code 
                where user_type = '03' and up.status != '99' and user_domain = 'C' and up.create_time ${time_condition} and customer_code in 
                (select cp.customer_code from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id where ca.approval_state = 'APPROVAL' and cp.status = 'A' and cp.cust_type = 'C')"""
            },
            "personal user": """select count(*) from user_profile up inner join user_user_customer uuc on up.user_code = uuc.user_code 
            where user_type = '01' and up.status != '99' and user_domain = 'C' and up.create_time ${time_condition}""",
            "technology partner": {
                "main": """select count(*) from user_profile up inner join user_user_customer uuc on up.user_code = uuc.user_code 
                where user_type = '03' and up.status != '99' and user_domain = 'C' and up.create_time ${time_condition}
                and customer_code in (select cp.customer_code from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id
                where ca.approval_state = 'APPROVAL' and cp.status = 'A' and cp.cust_type = 'T')""",
                "son": """select count(*) from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id
                where ca.approval_state = 'APPROVAL' and cp.status = 'A' and cp.cust_type = 'T' and ca.approval_date ${time_condition}"""
            },
            "business partner": {
                "main": """select count(*) from user_profile up inner join user_user_customer uuc on up.user_code = uuc.user_code
                where user_type = '03' and up.status != '99' and user_domain = 'C' and up.create_time ${time_condition}
                and customer_code in (select cp.customer_code from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id
                where ca.approval_state = 'APPROVAL' and cp.status = 'A' and cp.cust_type = 'B')""",
                "son": """select count(*) from customer_profile cp inner join customer_approval ca on cp.cust_id = ca.cust_id
                where ca.approval_state = 'APPROVAL' and cp.status = 'A' and cp.cust_type = 'B' and ca.approval_date ${time_condition}"""
            }
        },
        "User Status": {
            "total users": """select count(distinct up.*) from account_num_identity ani join user_profile up on ani.email = up.email
            where ani.status in ('00', '02', '04') and up.status != '99' and up.user_domain = 'C'""",
            "active users": """select count(distinct up.user_code) from account_num_identity ani join user_profile up on ani.email = up.email
            join user_session us ON us.user_code = up.user_code where ani.STATUS in ( '00', '02', '04' ) 
            and up.STATUS != '99' and up.user_domain = 'C' and up.email not in ( '' ) and us.login_time ${time_condition}""",
        }

    }

    def date_reverse(self, date_str: str):
        """ 倒置时间字符串 """
        data_str_list = date_str.split('-')
        data_str_list.reverse()
        return '-'.join(data_str_list)

    def clear_and_send(self, locator, content, doc):
        """ 清空输入框并输入值 """
        self.send_keys(locator, Keys.CONTROL + "a", doc)
        self.send_keys(locator, Keys.BACK_SPACE, doc)
        self.send_keys(locator, content, doc)

    def get_sql_count_by_time(self, cmdopt, database_object, mode, sql, start_date: str, end_date: str = None):
        """ 跟据时间条件执行SQL """
        database = Commondata.db_config[cmdopt]['database'][0]
        data_object, pool = CommonTool(database_object).get_data_source(database, cmdopt)

        if mode == "less":
            time_condition_str = f"< '{start_date} 23:59:59'"
            sql = sql.replace('${time_condition}', time_condition_str)
            query_result = data_object.do_mysql(sql, pool)
        elif mode == "between":
            time_condition_str = f"between '{start_date} 00:00:00' and '{end_date} 23:59:59'"
            sql = sql.replace('${time_condition}', time_condition_str)
            query_result = data_object.do_mysql(sql, pool)
        else:
            raise Exception
        return query_result[0]['count(*)']

    def enter_customer_resource(self):
        doc = "进入Customer Resource页面"
        self.click_element(AdminDashboardsLocator.customer_resource_tab, doc)
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        time.sleep(3)

    def enter_users(self):
        doc = "进入Users页面"
        self.click_element(AdminDashboardsLocator.users_tab, doc)
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        time.sleep(3)

    def enter_user_status(self):
        doc = "进入User Status页面"
        self.click_element(AdminDashboardsLocator.user_status_tab, doc)
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        time.sleep(3)

    def enter_sales_amount(self):
        doc = "进入Sales Amount页面"
        self.click_element(AdminDashboardsLocator.sales_amount_tab, doc)
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        time.sleep(3)

    def enter_orders(self):
        doc = "进入Orders页面"
        self.click_element(AdminDashboardsLocator.orders_tab, doc)
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        time.sleep(3)

    def check_date_as_of_time_selector(self, edge_date: str, doc: str):
        """ 核对date as of 的单日期选择器
            tips: 期限日期以公有云的报表日期为准
        """
        current_date = datetime.date.today()
        # 默认选择当天的前一天
        default_date_str = str(current_date + timedelta(days=-1))
        date_as_of_input_value = self.get_element(AdminDashboardsLocator.date_as_of_time_selector_input, doc).get_attribute("value")
        assert date_as_of_input_value == self.date_reverse(default_date_str)

        edge_date = dt.strptime(edge_date, "%Y-%m-%d").date()

        # 输入不在可选范围内的日期并回车，恢复默认的时间
        self.clear_and_send(AdminDashboardsLocator.date_as_of_time_selector_input, self.date_reverse(str(edge_date + timedelta(days=-1))), doc)
        self.send_keys(AdminDashboardsLocator.date_as_of_time_selector_input, Keys.ENTER, doc)
        time.sleep(2)
        assert self.get_element(AdminDashboardsLocator.date_as_of_time_selector_input, doc).get_attribute("value") == self.date_reverse(default_date_str)
        time.sleep(2)

        # 可选范围内日期可点击
        self.clear_and_send(AdminDashboardsLocator.date_as_of_time_selector_input, self.date_reverse(str(edge_date)), doc)
        edge_date_option_xpath = "//td[contains(@title, '${date_opt}')]"
        selected_font_class = "antd4-picker-cell-selected"
        assert selected_font_class in self.get_element((By.XPATH, edge_date_option_xpath.replace("${date_opt}", str(edge_date))), doc).get_attribute("class")
        time.sleep(2)
        self.clear_and_send(AdminDashboardsLocator.date_as_of_time_selector_input, self.date_reverse(str(current_date + timedelta(days=-1))), doc)
        assert selected_font_class in self.get_element((By.XPATH, edge_date_option_xpath.replace("${date_opt}", str(current_date + timedelta(days=-1)))), doc).get_attribute("class")
        time.sleep(2)

    def check_date_between_time_selector(self, edge_date: str, doc: str):
        """ 核对date between的日期区间选择器 """
        current_date = datetime.date.today()
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        # 默认起始时间为当天的前七天，截止时间为当前的前一天
        default_start_date = current_date + timedelta(days=-7)
        default_end_date = current_date + timedelta(days=-1)
        date_between_default_start_value = self.get_element(AdminDashboardsLocator.date_between_time_selector_start_input, doc).get_attribute("value")
        date_between_default_end_value = self.get_element(AdminDashboardsLocator.date_between_time_selector_end_input, doc).get_attribute("value")
        assert date_between_default_start_value == self.date_reverse(str(default_start_date))
        assert date_between_default_end_value == self.date_reverse(str(default_end_date))

        edge_date = dt.strptime(edge_date, "%Y-%m-%d").date()

        # 输入边界范围前/后一天，回车，会跳到默认日期
        self.clear_and_send(AdminDashboardsLocator.date_between_time_selector_start_input, self.date_reverse(str(edge_date + timedelta(days=-1))), doc)
        self.send_keys(AdminDashboardsLocator.date_between_time_selector_start_input, Keys.ENTER, doc)
        time.sleep(1)
        assert self.get_element(AdminDashboardsLocator.date_between_time_selector_start_input, doc).get_attribute("value") == self.date_reverse(str(default_start_date))

        self.clear_and_send(AdminDashboardsLocator.date_between_time_selector_end_input, self.date_reverse(str(current_date)), doc)
        self.send_keys(AdminDashboardsLocator.date_between_time_selector_end_input, Keys.ENTER, doc)
        self.wait_eleinvisble(AdminDashboardsLocator.loading_anime, doc)
        assert self.get_element(AdminDashboardsLocator.date_between_time_selector_end_input, doc).get_attribute("value") == self.date_reverse(str(default_end_date))
        time.sleep(3)

        # 输入边界范围，日期可点击
        self.clear_and_send(AdminDashboardsLocator.date_between_time_selector_start_input, self.date_reverse(str(edge_date)), doc)
        time.sleep(1)
        date_option_xpath = "//td[contains(@title, '${date_opt}')]"
        selected_font_class = "antd4-picker-cell-selected"
        class_name = self.get_element((By.XPATH, date_option_xpath.replace("${date_opt}", str(edge_date))), doc).get_attribute("class")
        assert selected_font_class in self.get_element((By.XPATH, date_option_xpath.replace("${date_opt}", str(edge_date))), doc).get_attribute("class")

        self.clear_and_send(AdminDashboardsLocator.date_between_time_selector_end_input, self.date_reverse(str(default_end_date)), doc)
        assert selected_font_class in self.get_element((By.XPATH, date_option_xpath.replace("${date_opt}", str(default_end_date))), doc).get_attribute("class")

    def customer_resource_query_by_time(self):
        doc = "跟据时间区间查询Customer Resource"
        self.enter_customer_resource()

        # 核对默认时间范围
        default_start_date_str = self.date_reverse(
            self.get_element(AdminDashboardsLocator.customer_resource_start_time_input, doc).get_attribute("value"))
        default_end_date_str = self.date_reverse(
            self.get_element(AdminDashboardsLocator.customer_resource_end_time_input, doc).get_attribute("value"))
        current_date = datetime.date.today()
        assert str(current_date + timedelta(days=-7)) == default_start_date_str
        assert str(current_date + timedelta(days=-1)) == default_end_date_str
        time.sleep(3)

        # 输入时间区间进行查询
        self.clear_and_send(AdminDashboardsLocator.customer_resource_start_time_input,
                            self.query_param["Customer Resource"]["start_time"], doc)
        time.sleep(1)
        start_time_option_xpath = "//td[@title='${opt_value}' and contains(@class, 'view')]"
        self.click_by_js((By.XPATH, start_time_option_xpath.replace("${opt_value}", self.date_reverse(
            self.query_param["Customer Resource"]["start_time"]))), doc)
        time.sleep(1)

        self.clear_and_send(AdminDashboardsLocator.customer_resource_end_time_input,
                            self.query_param["Customer Resource"]["end_time"], doc)
        time.sleep(1)
        end_time_option_xpath = "//td[@title='${opt_value}' and contains(@class, 'view')]"
        self.click_by_js((By.XPATH, end_time_option_xpath.replace("${opt_value}", self.date_reverse(
            self.query_param["Customer Resource"]["end_time"]))), doc)
        time.sleep(3)
        start_date = datetime.datetime.strptime(self.date_reverse(self.query_param["Customer Resource"]["start_time"]),
                                                '%Y-%m-%d')
        end_date = datetime.datetime.strptime(self.date_reverse(self.query_param["Customer Resource"]["end_time"]),
                                              '%Y-%m-%d')
        table_data_list = self.get_elements(AdminDashboardsLocator.customer_resource_table_rows, doc)
        for data in table_data_list:
            date_value = datetime.datetime.strptime(self.date_reverse(data.find_element(By.XPATH, "./td[1]").text),
                                                    '%Y-%m-%d')
            assert date_value <= end_date and date_value >= start_date

    def customer_resource_query_by_time_and_keyword(self):
        doc = "跟据时间区间和关键字查询Customer Resource"
        self.enter_customer_resource()

        # 输入时间条件
        self.clear_and_send(AdminDashboardsLocator.customer_resource_start_time_input,
                            self.query_param["Customer Resource"]["start_time"], doc)
        time.sleep(1)
        start_time_option_xpath = "//td[@title='${opt_value}' and contains(@class, 'view')]"
        self.click_by_js((By.XPATH, start_time_option_xpath.replace("${opt_value}", self.date_reverse(
            self.query_param["Customer Resource"]["start_time"]))), doc)
        time.sleep(1)

        self.clear_and_send(AdminDashboardsLocator.customer_resource_end_time_input,
                            self.query_param["Customer Resource"]["end_time"], doc)
        time.sleep(1)
        end_time_option_xpath = "//td[@title='${opt_value}' and contains(@class, 'view')]"
        self.click_by_js((By.XPATH, end_time_option_xpath.replace("${opt_value}", self.date_reverse(
            self.query_param["Customer Resource"]["end_time"]))), doc)
        time.sleep(3)
        start_date = datetime.datetime.strptime(self.date_reverse(self.query_param["Customer Resource"]["start_time"]),
                                                '%Y-%m-%d')
        end_date = datetime.datetime.strptime(self.date_reverse(self.query_param["Customer Resource"]["end_time"]),
                                              '%Y-%m-%d')

        # 输入Customer Name
        self.send_keys(AdminDashboardsLocator.customer_resource_keyword_input,
                       self.query_param["Customer Resource"]["customer_name"], doc)
        time.sleep(1)
        self.click_element(AdminDashboardsLocator.customer_resource_search_button, doc)
        time.sleep(3)
        table_data_list = self.get_elements(AdminDashboardsLocator.customer_resource_table_rows, doc)
        for data in table_data_list:
            date_value = datetime.datetime.strptime(self.date_reverse(data.find_element(By.XPATH, "./td[1]").text),
                                                    '%Y-%m-%d')
            assert date_value <= end_date and date_value >= start_date
            customer_name_value = data.find_element(By.XPATH, "./td[2]").text
            assert self.query_param["Customer Resource"]["customer_name"] in customer_name_value
        time.sleep(3)

        # 输入User Account
        self.clear_and_send(AdminDashboardsLocator.customer_resource_keyword_input,
                            self.query_param["Customer Resource"]["user_account"], doc)
        self.click_element(AdminDashboardsLocator.customer_resource_search_button, doc)
        time.sleep(3)
        table_data_list = self.get_elements(AdminDashboardsLocator.customer_resource_table_rows, doc)
        for data in table_data_list:
            date_value = datetime.datetime.strptime(self.date_reverse(data.find_element(By.XPATH, "./td[1]").text),
                                                    '%Y-%m-%d')
            assert date_value <= end_date and date_value >= start_date
            user_account_value = data.find_element(By.XPATH, "./td[3]").text
            assert self.query_param["Customer Resource"]["user_account"] in user_account_value
        time.sleep(3)

        # 输入Product
        self.clear_and_send(AdminDashboardsLocator.customer_resource_keyword_input,
                            self.query_param["Customer Resource"]["product"], doc)
        self.click_element(AdminDashboardsLocator.customer_resource_search_button, doc)
        time.sleep(3)
        table_data_list = self.get_elements(AdminDashboardsLocator.customer_resource_table_rows, doc)
        for data in table_data_list:
            date_value = datetime.datetime.strptime(self.date_reverse(data.find_element(By.XPATH, "./td[1]").text),
                                                    '%Y-%m-%d')
            assert date_value <= end_date and date_value >= start_date
            product_value = data.find_element(By.XPATH, "./td[6]").text
            assert self.query_param["Customer Resource"]["product"] in product_value
        time.sleep(3)

    def users_check_CDT_time_selector(self):
        doc = "验证Cumulative Data Tracking模块时间条件选择器"
        self.enter_users()
        edge_date = "2022-10-31"
        self.check_date_as_of_time_selector(edge_date, doc)

    def users_check_DDT_time_selector(self):
        doc = "验证Daily Data Tracking模块时间条件选择器"
        self.enter_users()
        edge_date = "2022-10-31"
        self.check_date_between_time_selector(edge_date, doc)

    def user_status_check_ADT_time_selector(self):
        doc = "验证Average Data Tracking模块时间条件选择器"
        self.enter_user_status()
        edge_date = "2022-10-31"
        self.check_date_as_of_time_selector(edge_date, doc)

    def user_status_check_DDT_time_selector(self):
        doc = "验证Daily Data Tracking模块时间条件选择器"
        self.enter_user_status()
        edge_date = "2022-10-31"
        self.check_date_between_time_selector(edge_date, doc)

    def sales_amount_check_CDT_time_selector(self):
        doc = "验证Cumulative Data Tracking模块时间条件选择器"
        self.enter_sales_amount()
        edge_date = "2022-10-31"
        self.check_date_as_of_time_selector(edge_date, doc)

    def sales_amount_check_DDT_time_selector(self):
        doc = "验证Daily Data Tracking模块时间条件选择器"
        self.enter_sales_amount()
        edge_date = "2022-10-31"
        self.check_date_between_time_selector(edge_date, doc)

    def orders_check_CDT_time_selector(self):
        doc = "验证Cumulative Data Tracking模块时间条件选择器"
        self.enter_orders()
        edge_date = datetime.date.today() - relativedelta(years=3)
        self.check_date_as_of_time_selector(str(edge_date), doc)

    def orders_check_DDT_time_selector(self):
        doc = "验证Daily Data Tracking模块时间条件选择器"
        self.enter_orders()
        edge_date = datetime.date.today() - relativedelta(years=3)
        self.check_date_between_time_selector(str(edge_date), doc)

    def check_table_timezone(self):
        doc = "查看orders/Sales Amount/Users/User Status页面报表展示时区"

        timezone = self.assert_data["timezone"]

        self.enter_users()
        self.scroll_bar_operation(AdminDashboardsLocator.users_table_col_date)
        assert self.get_element(AdminDashboardsLocator.users_table_col_date, doc).text.endswith(timezone)
        time.sleep(2)

        self.enter_user_status()
        self.scroll_bar_operation(AdminDashboardsLocator.user_status_table_col_date)
        assert self.get_element(AdminDashboardsLocator.user_status_table_col_date, doc).text.endswith(timezone)
        time.sleep(2)

        self.enter_sales_amount()
        self.scroll_bar_operation(AdminDashboardsLocator.sales_amount_table_col_date)
        assert self.get_element(AdminDashboardsLocator.sales_amount_table_col_date, doc).text.endswith(timezone)
        time.sleep(2)

        self.enter_orders()
        self.scroll_bar_operation(AdminDashboardsLocator.orders_table_col_date)
        assert self.get_element(AdminDashboardsLocator.orders_table_col_date, doc).text.endswith(timezone)
        time.sleep(2)


